package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;


import com.domain.Member;


public class MemberDao {
	private static MemberDao instance = new MemberDao();

	public static MemberDao getInstance(){
		return instance;		
	}

	//생성자를 private로 해서 외부에서는 생성을 못하도록
	private MemberDao(){
	}
	
	//커넥션 풀로 커넥션할당받는 메소드
	private Connection getConnection() throws Exception{
		Context initCtx = new InitialContext();
		Context envCtx =(Context)initCtx.lookup("java:comp/env");
		DataSource ds=(DataSource)envCtx.lookup("jdbc/orcl");

		return ds.getConnection();
	}
	
		
	//회원가입 쿼리문 처리
	public void insertMember(Member member)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "";
		int point = 100;
		int level = 0;
		String phone = member.getPhone1()+member.getPhone2()+member.getPhone3();
		String birth = member.getBirth1()+member.getBirth2()+member.getBirth3();
		String email = member.getEmail()+"@"+member.getEmail1();
		
		try{
			conn = getConnection();
			sql = "insert into MEMBER values(?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getId());
			pstmt.setString(2, member.getPasswd());
			pstmt.setString(3, member.getName());
			pstmt.setString(4, phone);
			pstmt.setString(5, birth);
			pstmt.setString(6, member.getAddress());
			pstmt.setString(7, email);
			pstmt.setInt(8, point);
			pstmt.setTimestamp(9, member.getReg_date());
			pstmt.setInt(10, level);
			pstmt.setString(11, member.getQuestion());
			pstmt.setString(12, member.getAnswer());
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			execClose(null,pstmt,conn);
		}
	}

	//아이디 비밀번호 체크부분
	public int userCheck(String id, String passwd)throws Exception{

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql="";
		String dbpasswd="";
		int level = 0 ;
		int x = -1;

		try{
			conn = getConnection();
			sql = "select passwd,levels from MEMBER where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			/*System.out.println(id);*/
			rs = pstmt.executeQuery();

			while(rs.next()){
				dbpasswd = rs.getString("passwd");
				level = rs.getInt("levels");

			}
			if(dbpasswd.equals(passwd)){
				x=1; //패스워드가 일치하면 인증성공 1로
				System.out.println("일반회원");
				if(level==1){ //관리자 값 확인 후 관리자일 경우 x값을 2로
					x=2;
					System.out.println("관리자");
				}
			}else{
				x=0; //패스워드 불일치시 0으로
			}



		}catch(Exception e){
			e.printStackTrace();
		}finally{
			execClose(rs,pstmt,conn);
		}
		return x;
	}


	//회원정보
	public Member getMember(String id)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member=null;
		String sql="";

		try{
			conn=getConnection();
			sql="select * from Member where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				member=new Member();
				member.setId(rs.getString("id"));
				member.setPasswd(rs.getString("passwd"));
				member.setName(rs.getString("name"));
				member.setPhone(rs.getString("phone"));
				member.setBirth(rs.getString("birth"));
				member.setAddress(rs.getString("address"));
				member.setEmail(rs.getString("email"));
				member.setPoint(rs.getString("point"));
				member.setReg_date(rs.getTimestamp("reg_date"));
				
				String phone = rs.getString("phone");
				String birth = rs.getString("birth");
				
			/*	123456789
				
				hamburger". substring(4, 8)에서는 "urge"
				01011112222
				20140101
				
				*/
				
				
				
				String phone1 = phone.substring(0, 3);
				String phone2 = phone.substring(3, 7);
				String phone3 = phone.substring(7, 11);
				
				String birth1 = birth.substring(0, 4);
				String birth2 = birth.substring(4, 6);
				String birth3 = birth.substring(6, 8);
				
				member.setPhone1(phone1);
				member.setPhone2(phone2);
				member.setPhone3(phone3);
				
				member.setBirth1(birth1);
				member.setBirth2(birth2);
				member.setBirth3(birth3);
				
				
				
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			execClose(rs, pstmt, conn);

		}
		return member;
	}

	//정보수정
	//(회원정보수정은 비밀번호 , 이름, 전화번호, 주소 , 이메일 수정가능하도록)

	public void updateMember(Member member)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		String phone = member.getPhone1()+member.getPhone2()+member.getPhone3();
		
		
		try{
			conn=getConnection();
			sql="update MEMBER set passwd=?,name=?,phone=?,address=?,email=? where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getPasswd());
			pstmt.setString(2, member.getName());
			pstmt.setString(3, phone);
			pstmt.setString(4, member.getAddress());
			pstmt.setString(5, member.getEmail());
			pstmt.setString(6, member.getId());

			pstmt.executeUpdate();//업데이트 하는 쿼리므로 executeUpdate를 사용한다.

		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			execClose(null,pstmt,conn);
		}
	}
	//회원탈퇴

	public void deleteMember(String id)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;

		try{
			conn=getConnection();
			sql="delete from MEMBER where id =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);

			pstmt.executeUpdate();//삭제도 업데이트를 사용함

		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			execClose(null,pstmt,conn);
		}
	}


	//아이디 중복체크
	public int confirmId(String id)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		int x = -1;
		try{
			conn=getConnection();
			sql="select * from MEMBER where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();

			if(rs.next()){
				x = 1;
			System.out.println(x+"아이디존재");
			}else{
				x = -1;
			System.out.println(x+"아이디없음");
			}

		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			execClose(rs,pstmt,conn);
		}
		return x;
	}

	//아이디 찾기
	public String findid(String id,String birth1,String birth2,String birth3)throws Exception{

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql="";
		String findids = null;
		String birth = birth1+birth2+birth3;
		try{
			conn = getConnection();
			sql = "select id from MEMBER WHERE name=? and birth = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, birth);
			rs = pstmt.executeQuery();

			if(rs.next()){
				findids = rs.getString("id");
				System.out.println(findids);
			}else{
				findids = null;
			}

		}
		catch(Exception e){
			e.printStackTrace();
		}finally{
			execClose(rs,pstmt,conn);
		}
		return findids;

	}
	

	//비밀번호찾기
	public String findpasswd(String id, String question, String answer)throws Exception{

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql="";
		String findpasswd = null;
		try{
			conn = getConnection();
			sql = "select passwd from MEMBER WHERE id=? and passq = ? and passa=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, question);
			pstmt.setString(3, answer);
			rs = pstmt.executeQuery();

			if(rs.next()){
				findpasswd = rs.getString("passwd");
			}else{
				findpasswd = null;
			}

		}
		catch(Exception e){
			e.printStackTrace();
		}finally{
			execClose(rs,pstmt,conn);
		}
		return findpasswd;

	}
	//비밀번호 랜덤값 삽입
	public String randompass(String id, String passwd)throws Exception{

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql="";
		String randompasswd = randompass.randomPassword(10);

		try{
			conn = getConnection();
			sql = "update MEMBER set passwd=? WHERE id=?";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, randompasswd);
			pstmt.setString(2, id);
			
			/*rs = */pstmt.executeUpdate();
			
/*			if(rs.next()){
				randompasswd = rs.getString("passwd");
				System.out.println("ㅇㅇ");
			}else{
			}*/

		}
		catch(Exception e){
			e.printStackTrace();
		}finally{
			execClose(rs,pstmt,conn);
		}
		return randompasswd;
	}
	
	//자원반납하는메소드
	public void execClose(ResultSet rs, PreparedStatement pstmt, Connection conn)throws Exception{
		//자원정리
		if(rs !=null) try{rs.close();}catch(SQLException sqle){}
		if(pstmt !=null) try{pstmt.close();}catch(SQLException sqle){}
		if(conn !=null) try{conn.close();}catch(SQLException sqle){}
	}

}
